import pandas as pd
import xlwings as xw
import os, sys, re
from hm.common import argv_files, parse_argv
import shutil

limit = {
    "step35(14.3.1 step9)":{
        "CH1":37,
        "CH3":37,
        "CH4":45,
        "CH5":40,
        # "CH6":33,
        # "CH7":33,
    },
    "step36(14.3.1 step10)":{
        "CH2":40,
        "CH3":45,
        "CH4":40,
    },
    "step37(14.3.1 step8&11)":{
        "ch201":50,
        "ch202":60,
        "ch203":60,
        "CH1":40,
    }
}


# step35
id_1020 = [
    {
        "name": "step35(14.3.1 step9)",
        "slots":[
            ("Ch 213 (C)", "CH1"),
            ("Ch 205 (C)", "CH2"),
            ("Ch 212 (C)", "CH3"),
            ("Ch 204 (C)", "CH4"),
            ("通道18", "CH5"),
            ("Ch 218 (C)", "CH6"),
            ("通道45", "CH7"),
            ("Ch 217 (C)", "CH8"),
            ("Ch 216 (C)", "CH9"),
            ("通道47", "lab ambient1020"),
            ("通道10", "lab ambient1025"),
        ]
    },
    {
        "name": "step36(14.3.1 step10)",
        "slots":[
            ("通道44", "CH1"),
            ("通道41", "CH2"),
            ("Ch 214 (C)", "CH3"),
            ("Ch 219 (C)", "CH4"),
            ("通道22", "CH3-1"),
            ("通道19", "CH3-2"),
            ("通道17", "CH3-3"),
            ("通道23", "CH3-4"),
            ("通道20", "CH3-5"),
            ("通道21", "CH3-6"),
            ("通道24", "CH3-7"),
        ]
    },
    {
        "name": "step37(14.3.1 step8&11)", 
        "slots":[
            ("Ch 201 (C)", "ch201"),
            ("Ch 202 (C)", "ch202"),
            ("Ch 203 (C)", "ch203"),
            ("通道43", "CH1"),
        ]
    },

    # 其他
    { 
        "name": "other(14.3.1 step12)",
        "slots":[
            ("Ch 220 (C)", "step12 ch201"),
        ]
    }
]


# step35
id_1025 = [
    {
        "name": "step35(14.3.1 step9)",
        "slots":[
            ("通道25", "CH1"),
            ("通道26", "CH2"),
            ("通道40", "CH3"),
            ("通道39", "CH4"),
            ("通道38", "CH5"),
            ("通道28", "CH6"),
            ("通道29", "CH7"),
            ("通道30", "CH8"),
            ("通道31", "CH9"),
            ("通道47", "lab ambient1020"),
            ("通道10", "lab ambient1025"),
        ]
    },
    {
        "name": "step36(14.3.1 step10)",
        "slots":[
            ("通道15", "CH1"),
            ("通道14", "CH2"),
            ("通道13", "CH3"),
            ("通道16", "CH4"),
            ("通道04", "CH3-1"),
            ("通道05", "CH3-2"),
            ("通道06", "CH3-3"),
            ("通道07", "CH3-4"),
            ("通道08", "CH3-5"),
            ("通道33", "CH3-6"),
            ("通道37", "CH3-7"),
        ]
    },
    {
        "name": "step37(14.3.1 step8&11)",
        "slots":[
            ("通道02", "ch201"),
            ("通道03", "ch202"),
            ("通道01", "ch203"),
            ("通道34", "CH1"),
        ]
    },

    # 其他
    { 
        "name": "other(14.3.1 step12)",
        "slots":[
            ("通道32", "step12 ch201"),
        ]
    }
]


class FlukeCsv:
    def __init__(self, path):
        self.df = pd.read_csv(path, header=0, encoding='gbk')
        if "Record #"!=list(self.df.iloc[0].index)[0]:
            raise Exception("不是Fluke数据")
        self.df = self.df.drop(0)  



class ProCsv:
    def __init__(self, path):
        self.df = pd.read_csv(path, header=0, encoding='gbk')
        if "MCGS_Time"!=list(self.df.iloc[0].index)[0]:
            raise Exception("不是生产多通道数据")
        self.df = self.df.drop([0,1]) # 移除第2,3 行数据
        if "--filter" in sys.argv:
            self.df = self.df.iloc[::59]
            # print(self.df)


def get_data(export_path):
    '''
        合并所有csv数据
    '''
    fluke_df, product_df = [], []
    fn,pn = 0,0
    for path in argv_files(export_path): # 注意文件名排序
        try: 
            f = FlukeCsv(path)
            fluke_df.append(f.df) 
            fn += 1
            print(f"Fluke{fn}", os.path.basename(path))
        except:
            try:
                f = ProCsv(path)
                product_df.append(f.df)
                pn += 1
                print(f"product{pn}", os.path.basename(path))
            except:
                print("无效文件", path)
    # df.append方法用以在表尾中添加新的行，并返回追加后的数据对象，若追加的行中存在原数据没有的列，会新增一列，并用nan填充；若追加的行数据中缺少原数据某列，同样以nan填充
    # concat方法用以将两个或多个pandas对象根据轴（横向/纵向）进行拼接，concat函数是在pandas命名空间下的方法，因此需要通过pd.concat()的方式来引用。
    if fluke_df:
        fluke_df = pd.concat(fluke_df)
    if product_df:
        product_df = pd.concat(product_df)
    return fluke_df, product_df


def main(export_path):
    if os.path.isfile(export_path):
        save_dir = os.path.dirname(export_path)
    else:
        save_dir = export_path

    print(export_path)
    if 'output' in os.listdir(export_path):
        print("output 文件夹已存在")
        # continue
        shutil.rmtree(os.path.join(export_path, 'output'))
        print("已删除output")


    app = xw.App(visible=False, add_book=False)
    fluke_df, product_df = get_data(export_path)
        
    save_dir = os.path.join(save_dir, 'output')
    os.makedirs(save_dir, exist_ok=True)

    analyzer = id_1020
    analyzerID = parse_argv('--id')
    if analyzerID and "1025" in analyzerID:
        analyzer = id_1025

    # 初始化与环境温度对比的通道
    chanel_list = [7,8,9] # 通道编号
    chanel_col_list = [None for c in chanel_list] # 对应的表格列
    chanel_data_size = [None for c in chanel_list]
    chanel_limit = [3, 4, 4] # 差值上限

    for step in analyzer:
        # 每个测试步骤生成一个excel结果
        wb = app.books.add()
        wb.activate()
        sht = wb.sheets(1)
        sht.activate()
        col_num = 1
        for i,df in enumerate([fluke_df, product_df]):
            # 两个多通道温度监控仪数据在excel中用不同的列分开，fluke的在左边，生产温控仪在右边
            if isinstance(df, list) and not df: 
                print(f"没找到数据{i}!")
                continue
            times = df.iloc[..., 1-i]
            col_str = xw.utils.col_name(col_num)

            for slots in step["slots"]:
                key, slot = slots[0], slots[1]
                if key in df.columns:
                    # 写入时间列
                    sht.range(f'{col_str}6').value = [[x] for x in times.values]  # :{col_str}{times.size}
                    sht.range(f'{col_str}6:{col_str}{6+len(times.values)}').number_format = 'yyyy/m/d h:mm'
                    sht.range(f'{col_str}1').value = step["name"]
                    sht.range(f'{col_str}3').value = "Max"
                    sht.range(f'{col_str}4').value = "Upper Limit"
                    sht.range(f'{col_str}5').value = "Result"
                    col_num += 1
                    have_data = True
                    break
            else:
                have_data = False
            
            for slots in step["slots"]:
                key, slot = slots[0], slots[1]
                if key in df.columns:
                    col_str = xw.utils.col_name(col_num)
                    # 写入方案对应的点位
                    sht.range(f'{col_str}1').value = slot
                    # 写入传感器通道
                    # sht.range(f'{col_str}2').value = key
                    data = df[key]
                    # 写入温度值
                    sht.range(f'{col_str}6').value = [[x] for x in data.values] #:{col_str}{data.size} # 注意是[x]
                    # 写入最大值
                    sht.range(f'{col_str}3').formula = f"=MAX({col_str}6:{col_str}{data.size+5})"
                    # 获取上限值
                    upper_limit = 'N/A'
                    if step['name'] in limit and slot in limit[step['name']]:
                        upper_limit = limit[step['name']][slot]
                    # 写入上限值
                    sht.range(f'{col_str}4').value = upper_limit
                    # 写入 pass/fail
                    if upper_limit !='N/A':
                        # sht.range(f'{col_str}5').formula = f'=IF({col_str}3<{upper_limit}, "Passed", "Failed")' # 注意公式需要用双引号
                        sht.range(f'{col_str}5').formula = f'=IF({col_str}3<{col_str}4, "Passed", "Failed")'
                    col_num += 1
                        
                    # ERME， DILN 和labambient对比
                    if step["name"]=="step35(14.3.1 step9)":
                        # 保存ERME，DILN通道所在的表格列
                        for i, chanel in enumerate(chanel_list):
                            if slot == f"CH{chanel}":
                                chanel_col_list[i] = xw.utils.col_name(col_num-1)    
                                chanel_data_size[i] = data.size
                                break        
                               
                        if slot=="lab ambient1025" and None not in chanel_col_list:
                            data_size = data.size 
                            # 2024/11/22
                            # 计算两个lab_ambient均值
                            lab_ambient_1020_col_str = xw.utils.col_name(col_num-2)
                            lab_ambient_1025_col_str = xw.utils.col_name(col_num-1)
                            lab_ambient_avr_col_str = xw.utils.col_name(col_num)
                            # 写标题
                            sht.range(f'{lab_ambient_avr_col_str}1').value = f"lab ambient average"
                            # 写均值
                            sht.range(f'{lab_ambient_avr_col_str}6').value = [[f"=({lab_ambient_1020_col_str}{x+6}+{lab_ambient_1025_col_str}{x+6})/2"] for x in range(data_size)]
                            col_num += 1
                    
                            for index, chanel in enumerate(chanel_list):          
                                data_size2 = max(chanel_data_size[index], data_size)
                                # 计算lab ambient和ERME(CH7)的差值的最大值是否超过3℃"
                                ERME_lab_col_str = xw.utils.col_name(col_num)
                                # 写标题
                                sht.range(f'{ERME_lab_col_str}1').value = f"lab ambient - CH{chanel}"
                                # 最大差值
                                sht.range(f'{ERME_lab_col_str}3').value = f"=MAX(MAX({ERME_lab_col_str}6:{ERME_lab_col_str}{data_size2+5}), ABS(MIN({ERME_lab_col_str}6:{ERME_lab_col_str}{data_size2+5})))"
                                # 限值
                                sht.range(f'{ERME_lab_col_str}4').value = chanel_limit[index] # 上限
                                # 结果
                                sht.range(f'{ERME_lab_col_str}5').formula = f'=IF({ERME_lab_col_str}3<{ERME_lab_col_str}4, "Passed", "Failed")'
                                # for x in range(len(data.values)): # 这样写速度会很慢
                                # 写lab ambient-ERME(CH7)
                                # sht.range(f'{ERME_lab_col_str}6').value = [[f"={col_str}{x+6}-{chanel_col_list[index]}{x+6}"] for x in range(data_size)]
                                sht.range(f'{ERME_lab_col_str}6').value = [[f"={lab_ambient_avr_col_str}{x+6}-{chanel_col_list[index]}{x+6}"] for x in range(data_size2)]
                                col_num += 1
            # 换文件，插入空列
            if have_data:
                col_num+=1
        save_path = os.path.join(save_dir, step['name']+".xlsx")
        wb.save(save_path)
        wb.close()
    app.quit()



if __name__=="__main__":
    '''
        运行方法：python env_temp.py csv数据所在文件夹
        运行结果：保存在csv数据所在文件夹下的output文件夹
    '''
    
    # export_path = input_paths = input("输入csv文件所在目录或文件： ").strip('"')
    # main(export_path)

    # for xx in range(1,6): # case1 to case5
    for xx in range(6,8): # case6 to case7
        for mm in range(1,3): # voltage1, voltage2
            id_ = "45001020"
            analyzerID = parse_argv('--id')
            if analyzerID and "1025" in analyzerID:
                id_ = "45001025"
            export_path = f"E:\\vitros450\\环境测试\\Environmental Test Notebook\\3-Objective Evidence\\{id_}\\ODBC-Environmental-T2\\case{xx}\\step35_36_37\\voltage{mm}_rawdata"
            main(export_path)
            
            



